CREATE TABLE #temp
(
	ID VARCHAR(50),
	Name VARCHAR(200),
	Make VARCHAR(50),
	Model VARCHAR(50),
	StationID VARCHAR(50),
	StationName VARCHAR(200),
	Latitude FLOAT,
	Longitude FLOAT,
	LineID VARCHAR(50),
	LineName VARCHAR(200),
	NominalVoltage FLOAT
)

INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('CARS013', 'SHANNON BANK A', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2913', 'Shannon', 34.3282275, -85.09419202, 'CARROLL 13', 'CARROLL 13', 4.16)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('CARSB13', 'SHANNON BANK B', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '1913', 'SHANNON', 34.3282275, -85.09419202, 'CARROLL 13 B', 'CARROLL 13 B', 4.16)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('CENS007', 'HAMPTON', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '1320', 'HAMPTON', 33.38282396, -84.27146911, 'CENTRAL GA 7', 'CENTRAL GA 7', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('CENS013', 'LOCUST GROVE', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '1311', 'LOCUS GROVE', 33.36070622, -84.12060398, 'CENTRAL GA 13', 'CENTRAL GA 13', 12)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('CENS024', 'GREENWOOD PARK', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '1493', 'GREENWOOD PARK', 33.418561, 84.17812571, 'CENTRAL GA 24', 'CENTRAL GA 24', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('COBG099', 'AZALEA SOLAR', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '10478', 'AZALEA SOLAR', 32.98115856, -82.59171983, 'COBB 99', 'COBB 99', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('COWS022', 'FLAT CREEK', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '5545', 'FLAT CREEK', 33.35007896, -84.54932632, 'Coweta-Fayette 22', 'Coweta-Fayette 22', 12)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('COWSB20', 'HIGHWAY 54 BANK B', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2644', 'HIGHWAY 54', 33.44925308, -84.51941681, 'COWETA-FAYETTE 20 B', 'COWETA-FAYETTE 20 B', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('FLIS026', 'WEYERHAEUSER', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2074', 'WEYERHAEUSER', 32.251294, -84.071908, 'FLINT 26', 'FLINT 26', 13.8)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('GRES001', 'WESTGATE', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '6010', 'WESTGATE', 33.70521543, -84.59870005, 'GREYSTONE 01', 'GREYSTONE 01', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('GRES031', 'BUZZARD ROOST BANK A', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '8067', 'BUZZARD ROOST', 33.7422853, -84.59235103, 'GREYSTONE 31', 'GREYSTONE 31', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('GRESB31', 'BUZZARD ROOST BANK B', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '8067', 'BUZZARD ROOST', 33.7422853, -84.59235103, 'GREYSTONE 31 B', 'GREYSTONE 31 B', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('JACS080', 'AIRGAS', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '5558', 'AIRGAS (PENDERGRASS)', 34.16518781, -83.63581084, 'JACKSON 80', 'JACKSON 80', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('JACS089', 'VALENTINE FARMS', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '8915', 'VALENTINE FARMS', 34.17944626, -83.64690519, 'JACKSON 89', 'JACKSON 89', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('JACSB44', 'SAVAGE ROAD BANK B', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '7946', 'SAVAGE ROAD', 33.98591478, -83.51199258, 'JACKSON 44 B', 'JACKSON 44 B', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('JEFS029', 'MESENA', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '5870', 'MESENA', 33.45294695, -82.53955512, 'JEFFERSON 29', 'JEFFERSON 29', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('MITS007', 'DOLES', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2803', 'DOLES', 31.70507, -83.884551, 'MITCHELL 07', 'MITCHELL 07', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('OKES013', 'NAHUNTA', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2200', 'NAHUNTA', 31.216873, -81.971871, 'OKEFENOKE 13', 'OKEFENOKE 13', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('OPCS001', 'PQ METER', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '1', 'OPC MAIN CAMPUS', 33.8448, -84.2383, 'OPC MAIN CAMPUS', 'OPC MAIN CAMPUS', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('PERVIS ROAD', 'PERVIS ROAD', 'ELECTRO INDUSTRIES', 'NEXUS 1500 +', '1882', 'PERVIS ROAD', 32.97744034, -82.48203952, 'AZALEA SOLAR', 'AZALEA SOLAR', 46)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('RABURN GAP', 'RABURN GAP', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '10126', 'RABURN GAP', 34.9537492, -83.37712408, 'RABURN GAP', 'RABURN GAP', 115)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('RAYS004', 'WASHINGTON', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '799', 'WASHINGTON', 33.73142556, -82.75122204, 'RAYLE 04', 'RAYLE 04', 12)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('RAYS007', 'WEST LINCOLNTON', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '8702', 'WEST LINCOLNTON', 33.79345204, -82.51175655, 'RAYLE 07', 'RAYLE 07', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('RAYS010', 'SWORDS', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '7408', 'SWORDS', 33.58448875, -83.28244993, 'RAYLE 10', 'RAYLE 10', 12)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('RAYS016', 'DELHI', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2328', 'DELHI', 33.91483697, -82.67339126, 'RAYLE 16', 'RAYLE 16', 12)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('SATS016_46KV CAP', 'BIG CREEK', 'ELECTRO INDUSTRIES', 'NEXUS 1252', '2111', 'BIG CREEK', 31.66220689, -82.42841986, 'CAP BANK', 'CAP BANK', 46)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('SAWS001', 'HYLTON', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '10454', 'HYLTON', 34.19863121, -84.13179326, 'SAWNEE 01', 'SAWNEE 01', 12)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('SIMG001', 'EAST SOCIAL CIRCLE', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2027', 'EAST SOCIAL CIRCLE', 33.67409897, -83.67910767, 'SIMON SOLAR', 'SIMON SOLAR', 115)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('SOUTH HAZELHURST', 'SOUTH HAZELHURST', 'ELECTRO INDUSTRIES', 'NEXUS 1500', '2099', 'SOUTH HAZLEHURST', 31.81152916, -82.56645966, 'SOUTH HAZLEHURST 25KV SOLAR', 'SOUTH HAZLEHURST 25KV SOLAR', 25)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('SR HAZLEHURST', 'SR HAZLEHURST ', 'ELECTRO INDUSTRIES', 'NEXUS 1500 +', '2099', 'SOUTH HAZLEHURST', 31.81152916, -82.56645966, 'SR HAZLEHURST 230KV SOLAR', 'SR HAZLEHURST 230KV SOLAR', 230)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('WALS026', 'TRANSCO-125', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '5908', 'TRANSCO-125', 33.83119227, -83.68986514, 'WALTON 26', 'WALTON 26', 4.16)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('WALSB26', 'TRANSCO-125 B', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '5908', 'TRANSCO-125', 33.83119227, -83.68986514, 'WALTON 26 B', 'WALTON 26 B', 12)
INSERT INTO #temp(ID, Name, Make, Model, StationID, StationName, Latitude, Longitude, LineID, LineName, NominalVoltage) VALUES('WASS010', 'ROBINS SPRINGS', 'ELECTRO INDUSTRIES', 'NEXUS 1272', '2216', 'ROBINS SPRINGS', 33.00520801, -82.87976189, 'WASHINGTON 10', 'WASHINGTON 10', 25)

INSERT INTO MeterLocation(AssetKey, Name, Latitude, Longitude)
SELECT DISTINCT StationID, StationName, Latitude, Longitude
FROM #temp

INSERT INTO Meter(MeterLocationID, AssetKey, Name, Make, Model, TimeZone)
SELECT MeterLocation.ID, #temp.ID, #temp.Name, #temp.Make, #temp.Model, 'UTC' TimeZone
FROM
	#temp JOIN
	MeterLocation ON MeterLocation.AssetKey = #temp.StationID

INSERT INTO Line(AssetKey, Description, VoltageKV, Length, ThermalRating)
SELECT DISTINCT LineID, LineName, NominalVoltage, 0 Length, 0 ThermalRating
FROM #temp

INSERT INTO MeterLocationLine(MeterLocationID, LineID)
SELECT MeterLocation.ID, Line.ID
FROM
	#temp JOIN
	MeterLocation ON MeterLocation.AssetKey = #temp.StationID JOIN
	Line ON Line.AssetKey = #temp.LineID

INSERT INTO MeterLine(MeterID, LineID, LineName)
SELECT Meter.ID, Line.ID, #temp.LineName
FROM
	#temp JOIN
	Meter ON Meter.AssetKey = #temp.ID JOIN
	Line ON Line.AssetKey = #temp.LineID

DELETE FROM MeterAssetGroup
WHERE MeterID IN
(
	SELECT Meter.ID
	FROM
		#temp JOIN
		Meter ON Meter.AssetKey = #temp.ID
)

INSERT INTO AssetGroup(Name)
VALUES('GTC_PQMeters')

INSERT INTO MeterAssetGroup(MeterID, AssetGroupID)
SELECT Meter.ID, AssetGroup.ID
FROM
	#temp JOIN
	Meter ON Meter.AssetKey = #temp.ID JOIN
	AssetGroup ON AssetGroup.Name = 'GTC_PQMeters'

DROP TABLE #temp